USE QUANLYNHANVIEN
-- tao table DEAN
IF EXISTS ( SELECT * FROM DBO.SYSOBJECTS WHERE NAME ='DEAN')
BEGIN
DROP TABLE DEAN
END
GO
	CREATE TABLE DEAN
(	TENDA			NVARCHAR(30),
	MADA			INT,
	DDIEM_DA		NVARCHAR(30),
	PHONG			INT
		PRIMARY KEY (MADA)			
)
	INSERT INTO		DEAN(TENDA,MADA,DDIEM_DA,PHONG)
	VALUES				('SAN PHAM Y',2,'NHATRANG',1)
	INSERT INTO		DEAN(TENDA,MADA,DDIEM_DA,PHONG)
	VALUES				('SAN PHAM Z',3,'TP HCM',5)
	INSERT INTO		DEAN(TENDA,MADA,DDIEM_DA,PHONG)
	VALUES				('SAN PHAM X',5,'VUNG TAU',1)
	INSERT INTO		DEAN(TENDA,MADA,DDIEM_DA,PHONG)
	VALUES				('TIN HOC HOA',10,'HA NOI',4)
	INSERT INTO		DEAN(TENDA,MADA,DDIEM_DA,PHONG)
	VALUES				('Hoa Hoc',40,'Bien Hoa',5)
-- kiem tra
	SELECT * FROM DEAN

--tao table DIADIEM_PHG
IF EXISTS ( SELECT * FROM DBO.SYSOBJECTS WHERE NAME ='DIADIEM_PHG')
BEGIN
DROP TABLE DIADIEM_PHG
END
GO
	CREATE TABLE DIADIEM_PHG
(	MAPHG			INT,
	DIADIEM			NVARCHAR(20)
		PRIMARY KEY (MAPHG,DIADIEM)			)
	INSERT INTO		DIADIEM_PHG(MAPHG,DIADIEM)
	VALUES			(1,'VUNG TAU')
	INSERT INTO		DIADIEM_PHG(MAPHG,DIADIEM)
	VALUES			(2,'TP HCM')
	INSERT INTO		DIADIEM_PHG(MAPHG,DIADIEM)
	VALUES			(3,'NHA TRANG')
	INSERT INTO		DIADIEM_PHG(MAPHG,DIADIEM)
	VALUES			(4,'TP HCM')
	INSERT INTO		DIADIEM_PHG(MAPHG,DIADIEM)
	VALUES			(5,'HA NOI')
	INSERT INTO		DIADIEM_PHG(MAPHG,DIADIEM)
	VALUES			(5,'TP HCM')
-- kiem tra
	SELECT * FROM DIADIEM_PHG

--tao table NHANVIEN
IF EXISTS ( SELECT * FROM DBO.SYSOBJECTS WHERE NAME ='NHANVIEN')
BEGIN
DROP TABLE NHANVIEN
END
GO
	CREATE TABLE NHANVIEN
(	HONV			NVARCHAR(10),
	TENLOT			NVARCHAR(20),
	TENNV			NVARCHAR(10),
	MANV			NVARCHAR(10),
	NGSINH			DATETIME,
	DCHI			NVARCHAR(100),
	PHAI			NVARCHAR(5),
	LUONG			NUMERIC,
	MA_NQL			NVARCHAR(10),
	PHG				INT
		PRIMARY KEY (MANV)			)		
	INSERT INTO		NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
	VALUES			('CAO','SI','KI','123123456','09/08/1986','123 LE LOI Q1 TPHCM','NAM',15000,'123456789',5)
	INSERT INTO		NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
	VALUES			('DINH','BA','TIEN','123456789','09/01/1955','731 TRAN HUNG DAO Q1 TPHCM','NAM',30000,'123456789',5)
	INSERT INTO		NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
	VALUES			('TRUONG','LE','DOAN','147852369','05/15/1986','22/41/2 LE VAN THO F11 GO VAP','NU',16500,'147852369',1)
	INSERT INTO		NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
	VALUES			('NGUYEN','THANH','TUNG','333445555','08/12/1945','638 NGUYEN VAN CU Q5 TPHCM','NAM',40000,'123456789',5)
	INSERT INTO		NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
	VALUES			('LE','HUYNH','DUC','666884444','01/06/1986','126 NGUYEN VAN CU Q5 TPHCM','NAM',27000,'666884444',1)
	INSERT INTO		NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
	VALUES			('VUONG','NGOC','QUYEN','888665555','09/15/1965','414 NO TRANG LONG Q BINH THANH TRPCM','NAM',29400,'999887777',4)
	INSERT INTO		NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
	VALUES			('LE','THI','NHAN','987654321','06/20/1931','291 HO VAN HUE Q PHU NHUAN TPHCM','NU',43000,'123456789',5)
	INSERT INTO		NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
	VALUES			('VO','DUC','MINH','987987987','09/16/1985','414 NO TRANG LONG Q BINH THANH TPHCM','NAM',30000,'123456789',5)
	INSERT INTO		NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
	VALUES			('BUI','THUY','VU','999887777','07/19/1958','332 NGUYEN THAI HOC Q1 TPHCM','NAM',25000,'123456789',4)
-- kiem tra
	SELECT * FROM NHANVIEN

-- tao table PHONGBAN
IF EXISTS ( SELECT * FROM DBO.SYSOBJECTS WHERE NAME ='PHONGBAN')
BEGIN
DROP TABLE PHONGBAN
END
GO
	CREATE TABLE PHONGBAN
(	TENPHG			NVARCHAR(30),
	MAPHG			INT,
	TRPHG			NVARCHAR(10),
	NG_NHANCHUC		DATETIME
		PRIMARY KEY	(MAPHG)			)
	INSERT INTO		PHONGBAN(TENPHG,MAPHG,TRPHG,NG_NHANCHUC)
	VALUES			('Quan ly',1,'666884444','06/19/1971')
	INSERT INTO		PHONGBAN(TENPHG,MAPHG,TRPHG,NG_NHANCHUC)
	VALUES			('To chuc',2,'987987987','01/15/2001')
	INSERT INTO		PHONGBAN(TENPHG,MAPHG,TRPHG,NG_NHANCHUC)
	VALUES			('nhan su',3,'987987987','02/03/2001')
	INSERT INTO		PHONGBAN(TENPHG,MAPHG,TRPHG,NG_NHANCHUC)
	VALUES			('Dieu hanh',4,'123456789','01/01/1978')
	INSERT INTO		PHONGBAN(TENPHG,MAPHG,TRPHG,NG_NHANCHUC)
	VALUES			('NgHien cuu',5,'333445555','05/22/1978')
-- kiem tra
	SELECT * FROM PHONGBAN

-- tao table THANNHAN
IF EXISTS ( SELECT * FROM DBO.SYSOBJECTS WHERE NAME ='THANNHAN')
BEGIN
DROP TABLE THANNHAN
END
GO
	CREATE TABLE THANNHAN
(	MA_NVIEN			NVARCHAR(10),
	TENTN				NVARCHAR(30),
	PHAI				NVARCHAR(5),
	NGSINH				DATETIME,
	QUANHE				NVARCHAR(20)
		PRIMARY KEY (MA_NVIEN,TENTN)			)
	INSERT INTO		THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE)
	VALUES			('333445555','CHAU LONG','NAM','01/20/2005','CHONG')
	INSERT INTO		THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE)
	VALUES			('333445555','KHANH','NAM','10/25/1973','CON TRAI')
	INSERT INTO		THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE)
	VALUES			('333445555','NGOC PHI','NU','05/04/1976','CON GAI')
	INSERT INTO		THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE)
	VALUES			('987654321','DANG','NAM','02/29/1932','VO CHONG')
	INSERT INTO		THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE)
	VALUES			('999887777','TOAN','NAM','02/01/1986','CHONG')
-- kiem tra 
	SELECT * FROM THANNHAN

-- tao table PHANCONG
IF EXISTS ( SELECT * FROM DBO.SYSOBJECTS WHERE NAME ='PHANCONG')
BEGIN
DROP TABLE PHANCONG
END
GO
	CREATE TABLE	PHANCONG
(	MA_NVIEN			NVARCHAR(10),
	SODA				INT,
	THOIGIAN			NVARCHAR(10)
		PRIMARY KEY	(MA_NVIEN,SODA)			)
	INSERT INTO		PHANCONG(MA_NVIEN,SODA,THOIGIAN)
	VALUES			('123123456',2,' ')
	INSERT INTO		PHANCONG(MA_NVIEN,SODA,THOIGIAN)
	VALUES			('123123456',40,' ')
	INSERT INTO		PHANCONG(MA_NVIEN,SODA,THOIGIAN)
	VALUES			('123456789',40,'10.00')
	INSERT INTO		PHANCONG(MA_NVIEN,SODA,THOIGIAN)
	VALUES			('147852369',40,' ')
	INSERT INTO		PHANCONG(MA_NVIEN,SODA,THOIGIAN)
	VALUES			('333445555',2,' ')
	INSERT INTO		PHANCONG(MA_NVIEN,SODA,THOIGIAN)
	VALUES			('666884444',40,' ')
	INSERT INTO		PHANCONG(MA_NVIEN,SODA,THOIGIAN)
	VALUES			('888665555',2,' ')
	INSERT INTO		PHANCONG(MA_NVIEN,SODA,THOIGIAN)
	VALUES			('987654321',40,' ')
	INSERT INTO		PHANCONG(MA_NVIEN,SODA,THOIGIAN)
	VALUES			('987987987',40,' ')
	INSERT INTO		PHANCONG(MA_NVIEN,SODA,THOIGIAN)
	VALUES			('999887777',40,' ')
-- kiem tra
	SELECT * FROM PHANCONG


